Open topic with navigation

Database reference guide

 HOME

Data type

This section introduces the basic data types that are available within Engine:

  • TEXT
  • UNICODE
  • INTEGER
  • BIGINT
  • REAL
  • DATE
  • TIME
  • DATETIME

Columns and Fields

The terms "Column" and "Field" are used interchangeably when working with Engine and can refer to either loaded data or derived data.

TEXT

Used to store alphanumeric data.

The text data type has several sub categories:

  • Fixed Width: The width of the field is specified at the time of creation, and each record in the field consumes the same amount of memory. For small width fields, or fields where all the data is a uniform size, this is an efficient storage mechanism.
  • Variable Width: For data that varies in width from record to record, and where the average width is significantly less than the maximum, using variable width storage can give great savings on storage space at no cost to performance. Engine will automatically decide to store a text field as variable width based on configurable settings.
  • Array Field: For survey or multiple-response data, Engine supports a sub-type of text field referred to as an array field. This field allows a question, and all multiple-choice answers to it, to be stored within one field. For example, the question, "Which Credit Cards do you have?" could have many answers. A person have one, none or many of the possible answers.

Array fields allow these responses to be coded and then stored in such a way that the data can be easily analyzed.

The underlying storage mechanism is that used for text fields, and as such, array fields can be stored as either fixed Array or variable Array.

An Array field is created by creating a normal text field and specifying an array format. Fixed or variable width storage can be explicitly set on a field by field basis.

UNICODE

The Unicode data type is used to store double byte character sets. For more information on the scope of usage, please refer to the Language Data Support Best Practice Guide.

INTEGER

Integer fields are used to store numeric data that have no decimal values. It is important to note that Integer fields are stored as signed 32 bit integers. This gives them a range of

-2147483647 to 2147483647. For storage of data that will go beyond this range, a BIGINT format must be used but this would have storage considerations (see below)

BIGINT

BIGINT fields, introduced in the Engine release with Campaign Manager 6.2, are used to store numeric data that have no decimal values, where the value goes beyond the scope of the INTEGER data type. BIGINT columns are stored as signed 64-bit integers and are therefore double the disk storage of the INTEGER and should only be used when required based on the incoming data.

Note:
  • An Aggregation Function will always Result in a BIGINT column as the operation should be deterministic of the schema. That is to say that the data type result of an operation should not change based on the data type and as an Aggregate command has the scope to be a BIGINT, it must always be a BIGINT.
  • A Cerberus.ini setting "AggregateBigIntWidth" allows the definition of the width of an Aggregate function, so can be used to revert the width of a newly created BIGINT aggregate back to the previous default prior to BIGINT introduction, if that width is relied on for onward processing. This setting is one of a number of settings that can control the resultant width of data engineering functions.

REAL

Real fields are used to store integer or decimal numbers. The maximum precision is 6 decimal places. For example, the cost of a product, or a model score.

DATE

Date fields are used to store date data. Valid formats are:

- CCYYMMDD

- DDMMCCYY

- CCYYDDMM

- MMDDCCYY

- DDMMYY

- MMDDYY

- DDMMCCYY

TIME

Time fields are used to store Time Data. Valid formats are:

- HHMMSS

- HHMM

- HH:MM:SS

- HH:MM

DATETIME

Date Time fields are used to store date and time data in the same field. Valid formats are:

- CCYYMMDD HHMMSS

- CCYYMMDD HHMM

- CCYYMMDD HH:MM:SS

- CCYYMMDD HH:MM

- DDMMCCYY HHMMSS

- DDMMCCYY HHMM

- DDMMCCYY HH:MM:SS

- DDMMCCYY HH:MM

- CCYYDDMM HHMMSS

- CCYYDDMM HHMM

- CCYYDDMM HH:MM:SS

- CCYYDDMM HH:MM

- MMDDCCYY HHMMSS

- MMDDCCYY HHMM

- MMDDCCYY HH:MM:SS

- MMDDCCYY HH:MM

- DDMMYY HHMMSS

- DDMMYY HHMM

- DDMMYY HH:MM:SS

- DDMMYY HH:MM

- MMDDYY HHMMSS

- MMDDYY HHMM

- MMDDYY HH:MM:SS

- MMDDYY HH:MM

- DDMMMCCYY HHMMSS

- DDMMMCCYY HHMM

- DDMMMCCYY HH:MM:SS

- DDMMMCCYY HH:MM

  Online & Instructor-Led Courses | Training Videos | Webinar Recordings
© Alterian. All Rights Reserved. | Privacy Policy | Legal Notice